SQL query syntax for loop (Classic ASP)

Question

I have 4 tables and the following SQL query:

SELECT * FROM dbo.Synola, dbo.Stores, dbo.Fpa, dbo.Nomismata
WHERE
dbo.Stores.Store_id = dbo.Synola.Store_id
AND
dbo.Stores.fpa_id = dbo.Fpa.fpa_id
AND
dbo.Stores.nomisma_id = dbo.Nomismata.nomisma_id

The above works fine and without errors.

My problem is that when I am trying to loop the above query:

Currently, in my Stores TABLE, I have only 2 stores and I want in the loop to get results ONLY for 2 records. The 2 records with my 2 stores. But unfortunately I am receiving more than 2 records.

Which is the correct syntax of my query in a way to receive results only for my 2 stores in the loop?

If you're getting more than 2 result rows then there are *multiple* matching rows in these other tables. How are *we* or your SQL system meant to know *which* rows to pick from the other tables for each store? Examine your data, formulate *specific* rules based on the *data* stored in these other tables for how to pick one row per store and then, if it's not clear how to integrate those rules into your query, at least *add them to your question*. Sample data wouldn't hurt either when you're adding the explanation. — Damien_The_Unbeliever, Dec 20 '17 at 15:15

justiceorjustus · Accepted Answer · 2017-12-20T15:58:25.010

1

This should only retrieve data that's available in ALL tables. Therefore, it there's not a match to the main Stores table, then the result wont show.

SELECT *
FROM Stores s
JOIN Synola sy ON sy.Store_id = s.Store_id
JOIN Fpa f ON f.fpa_id = s.fpa_id
JOIN Nomismata n ON n.nomisma_id = s.nomisma_id

If you are getting more rows because there are more matches in other tables, then you need to look into adding more WHERE conditions or using another type of JOIN.

More info: https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

Edit: To see where you are getting multiple matches on your JOIN:

SELECT s.Store_id "Store_id from Store",
    ,sy.Store_id "Store_id from Synola"
    ,s.fpa_id "fpa_id from Store"
    ,f.fpa_id "fpa_id from Fpa"
    ,s.nonisma_id "nomisma_id from Store"   
    ,n.nonisma_id "nomisma_id from Nomismata"
FROM Stores s
JOIN Synola sy ON sy.Store_id = s.Store_id
JOIN Fpa f ON f.fpa_id = s.fpa_id
JOIN Nomismata n ON n.nomisma_id = s.nomisma_id

edited Dec 20 '17 at 15:58

answered Dec 20 '17 at 15:10

justiceorjustus

2,017
1
19
42

Again I am receiving more than 2 records. I have posted also an image with my tables structure.. – Freddakos Dec 20 '17 at 15:44
@user2986570 Then you are getting more than one match in `Synola`, `Fpa`, or `Nomismata` in the `ON` part of the `JOIN`. Either don't `JOIN` some of those tables if you don't have to, or add a `WHERE` condition to filter out the matches you don't want. – justiceorjustus Dec 20 '17 at 15:53
@user2986570 I added an additional snippet to help you find which table is getting the multiple matches. Technically, this is just to give you a visual. Because of how this type of join works, there is a multiple match in all three. – justiceorjustus Dec 20 '17 at 16:07
I am getting multiple matches from the table Synola – Freddakos Dec 20 '17 at 18:43
@user2986570 Okay, so at this point you need to add a condition to your `WHERE` clause to filter out the rows from Synola that you don't want. That, or don't join Synola if you don't have to for your needs i.e. you don't need any of the information from it. – justiceorjustus Dec 20 '17 at 18:53
I need all the information of table Synola! From the table Stores i need only the Store_Name, from the table Fpa i need only the fpa_pososto and from the table Nomismata i need only the nomisma – Freddakos Dec 20 '17 at 19:06
@user2986570 So you're saying that you need to make multiple rows from Synola into one row? You would have to do something like `STUFF`: https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server However, it's clear you should look into some of the basics of a relational database. That way this will be a bit easier to understand why you're getting the result you're getting. – justiceorjustus Dec 20 '17 at 19:18
Thank you for your help and your advice. – Freddakos Dec 20 '17 at 19:29
@justiceorjustus - `STUFF` is used to *remove a comma from the final result*. Whereas `FOR XML PATH` actually takes all of the separate results and concatenates them together. Thinking of that technique as being `STUFF` is to entirely focus on the wrong part. – Damien_The_Unbeliever Dec 21 '17 at 06:52

SQL query syntax for loop (Classic ASP)

1 Answers1