equi-join with two tables?

# equi-join with two tables?

• Tuesday, November 20, 2012 12:53 AM

I would like to join two table using equi-join. I have this query so far:

SELECT CD_INVENTORY.CD_NAME, CD_INVENTORY.IN_STOCK, PERFORMERS.PERF_NAME,CD_INVENTORY.PERF_ID,PERFORMERS.PERF_ID, PERFORMERS.TYPE_ID
FROM PERFORMERS,CD_INVENTORY
WHERE CD_INVENTORY.PERF_ID = PERFORMERS.PERF_ID;

It keeps asking for a prompt to enter a parameter value for CD_INVENTORY.PERF_ID and its not supposed to do that if done correctly.

Anyone know how to do this correctly? I have been changing bits and pieces trying to figure out where I went wrong.

Once you eliminate the impossible, whatever remains, no matter how improbable, must be the truth. - "Sherlock holmes" "speak softly and carry a big stick" - theodore roosevelt. Fear leads to anger, anger leads to hate, hate leads to suffering - Yoda. Blog - http://www.computerprofessions.co.nr

### All Replies

• Tuesday, November 20, 2012 1:26 AM
Moderator

The query seems to be OK. I would write it differently, though:

```SELECT Inv.CD_NAME, Inv.IN_STOCK, Perf.PERF_NAME,Inv.PERF_ID,Perf.PERF_ID, Perf.TYPE_ID
FROM PERFORMERS Perf INNER JOIN CD_INVENTORY Inv
ON Perf.PERF_ID = Inv.PERF_ID;```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Marked As Answer by Tuesday, November 27, 2012 3:43 PM
•
• Tuesday, November 20, 2012 1:35 AM

I know that query will work because I did something similar but iam doing this for a database design class and the teacher wants a equi-join on the statement instead of inner join.

note: iam using access for this assignment.

Once you eliminate the impossible, whatever remains, no matter how improbable, must be the truth. - "Sherlock holmes" "speak softly and carry a big stick" - theodore roosevelt. Fear leads to anger, anger leads to hate, hate leads to suffering - Yoda. Blog - http://www.computerprofessions.co.nr

• Tuesday, November 20, 2012 2:04 AM
Moderator

For Access you may have better luck asking in Access forum. Let me know if you want your thread to be moved there. For now, using your original query try switching around your WHERE condition, e.g.

WHERE Performers.Perf_ID = CD_INVENTORY.Perf_ID

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Tuesday, November 20, 2012 4:22 AM
Moderator

but iam doing this for a database design class and the teacher wants a equi-join on the statement instead of inner join.

That is an EQUI-JOIN what you have. Naomi's suggestion formatted:

```SELECT Inv.cd_name,
Inv.in_stock,
Perf.perf_name,
Inv.perf_id,
Perf.perf_id,
Perf.type_id
FROM   performers Perf
INNER JOIN cd_inventory Inv
ON Perf.perf_id = Inv.perf_id;  ```

• Tuesday, November 27, 2012 3:42 PM

I think the first answer about it being the correct query was fine because despite my problem I still received an A. I think maybe it might have been the relationship between my tables too. But whatever it was thanks for your help.

Once you eliminate the impossible, whatever remains, no matter how improbable, must be the truth. - "Sherlock holmes" "speak softly and carry a big stick" - theodore roosevelt. Fear leads to anger, anger leads to hate, hate leads to suffering - Yoda. Blog - http://www.computerprofessions.co.nr