Select multiple rows from MySQL tables for 1 user

Question

To simplify the explanation, I will try to simplify everything.

I have three SQL tables: Users , Certs , Serv

The Users table stores information about unique users.

The Certs table stores information about certificates and the UserId of the user who has this certificate (1 user can have several certificates)

The Serv table stores data on marine services and the UserId user (for example, the Certs table, 1 user can have several entries in the Serv table)

SAMPLE DATA

Users

 UserId Name 1 John 2 Lisa 

Certs

 Id UserId CertName 1 1 A 2 1 B 3 1 C 4 2 A 5 2 C 

Serv

 UserId Name 1 SA 1 SB 1 SC 1 SD 2 S2A 

I need to get output through PHP, for example (where UserId = 1), and for reality there will be more columns from each table (for example, more detailed information about certificates, such as release date, expiration date, place of release, etc. on the):

 Personal details: Name John Certificates: Certificate Id Certificate Name 1 A 2 B 3 C Sea Services: Sea Service Name SA SB SC SD 

But I have incorrect output, duplicate entries, which is associated with the union of two tables with UserId , in which there are several records with this UserId .

PHP code

 $users = $con->prepare(" select u.Name ,GROUP_CONCAT(c.Id SEPARATOR '<br>') AS Id ,GROUP_CONCAT(c.certsName SEPARATOR '<br>') AS certsName ,GROUP_CONCAT(s.Name SEPARATOR '<br>') AS Name from users u left join certs c on u.UserId = c.UserId left join serv s on u.UserId = s.UserId where u.UserId = ? "); $users->bind_param('i', $GetUserId); $users->execute(); $users->bind_result( $userName, $certId, $certName, $servName ); <?php while ($users->fetch()) { ?> <span>Personal Details</span> <div class="grid-group"> <div class="grid-column"> <div class="grid-item header">User Name </div> </div> <div class="grid-column"> <div class="grid-item"><?php echo $userName; ?></div> </div> </div> <span>Certificates</span> <div class="grid-group"> <div class="grid-column"> <div class="grid-item header">Certificate Id</div> <div class="grid-item header">Certificate Name</div> </div> <div class="grid-column"> <div class="grid-item"><?php echo $certId; ?></div> <div class="grid-item"><?php echo $certName; ?></div> </div> </div> <span>Sea Services</span> <div class="grid-group"> <div class="grid-column"> <div class="grid-item header">Sea Service Name</div> </div> <div class="grid-column"> <div class="grid-item"><?php echo $servName; ?></div> </div> </div> <?php } ?> 

You can check SQL FIDDLE to see which results are selected by duplicating rows.

Do you have any ideas how I can achieve the desired result without duplicates?

UPDATE

After using GROUP_CONCAT with DISTINCT This is still wrong. Imagine in a Serv table I have columns like: UserId , Name , Rank and Country

If the same user worked in different companies (this name in the example is the name of the company) with the same rating in different countries, he chooses the wrong data. For instance:

Services Table (SQL)

 UserId Name Rank Country 1 SA Captain USA 1 SB Captain USA 1 SC Captain RUS 1 SD Captain ENG 2 S2A Engineer USA 

If I use a query like this:

 select u.Name ,GROUP_CONCAT(distinct c.Id SEPARATOR '<br>') AS Id ,GROUP_CONCAT(distinct c.certsName SEPARATOR '<br>') AS certsName ,GROUP_CONCAT(distinct s.Name SEPARATOR '<br>') AS Name ,GROUP_CONCAT(distinct s.Rank SEPARATOR '<br>') AS Rank ,GROUP_CONCAT(distinct s.Country SEPARATOR '<br>') AS Country from users u left join certs c on u.UserId = c.UserId left join serv s on u.UserId = s.UserId where u.UserId = ? 

So GROUP_CONCAT(DISTINCT..) returns the following to me:

 ...... Sea Services: Sea Service Name Rank Country SA Captain USA SB RUS SC ENG SD 

Only the first row has a rank, and the first 3 rows return countries, but the database stores the ranks and countries for each row.

The complete desired result with this data should be:

 Personal details: Name John Certificates: Certificate Id Certificate Name 1 A 2 B 3 C Sea Services: Sea Service Name Rank Country SA Captain USA SB Captain USA SC Captain RUS SD Captain ENG 

You can check it on SQL FIDDLE

UPDATE 2

If I remove DISTINCT , I get the following output:

 Sea Service Name Rank Country SA Captain USA SA Captain USA SA Captain USA SB Captain USA SB Captain USA SB Captain USA SC Captain RUS SC Captain RUS SC Captain RUS SD Captain ENG SD Captain ENG SD Captain ENG 

If I use DISTINCT , I get the following:

 Sea Services: Sea Service Name Rank Country SA Captain USA SB RUS SC ENG SD 

But it should be:

 Sea Services: Sea Service Name Rank Country SA Captain USA SB Captain USA SC Captain RUS SD Captain ENG 

UPDATE 3

Imagine that I have a fixed column width and I have a long Sea Service Name that will be wrapped to a new line:

 Sea Service Name | Rank | Country -----------------|--------|--------- This is long Sea | Captain| USA Service Name |--------|--------- -----------------| Captain| RUS Other Name |--------|--------- -----------------| Captain| ENG Another long Sea |--------|--------- Service Name | Master | USA -----------------|--------|--------- Other Sea Serv | -----------------| 

As you can see, each column is divided, rows do not match. But it should be like 1 row. So I think I can not achieve this with GROUP_CONCAT , it seems like I need a different way.

What it looks like in reality:

Answer 1

Missing group by clause:

 select u.Name ,GROUP_CONCAT(distinct c.Id SEPARATOR '<br>') AS Id ,GROUP_CONCAT(distinct c.certsName SEPARATOR '<br>') AS certsName ,GROUP_CONCAT(distinct s.Name SEPARATOR '<br>') AS Name ,(SELECT GROUP_CONCAT(ss.Rank SEPARATOR '<br>') FROM users uu LEFT OUTER JOIN serv ss ON (uu.UserId = ss.UserId) WHERE uu.user_id = u.user_id) as Rank ,GROUP_CONCAT(distinct s.Country SEPARATOR '<br>') AS Country from users u left join certs c on u.UserId = c.UserId left join serv s on u.UserId = s.UserId where u.UserId = ? 

In addition, I added a different one from your GROUP_CONCAT , since you left a connection with several tables with short rows for each user, you will have several duplicates.

Answer 2

This looks like a crazy effort to avoid a few requests.

Keep it simple

You can execute the request:

 SELECT ... FROM users u LEFT JOIN certs c on u.UserId = c.UserId LEFT JOIN serv s on u.UserId = s.UserId WHERE u.UserId = ? 

And separate the certificates and services in the application logic.

Or just run two or three separate queries:

  SELECT * FROM users u WHERE u.UserId = ? SELECT * FROM certs c WHERE c.UserId = ? SELECT * FROM serv s WHERE s.UserId = ? 

Although there is overhead for 3 queries, with proper indexing, they will be insanely fast, and you have reduced the amount of redundant data changing hands.

These simple queries are easily debugged and understood. You have a very complex request, and even minor changes are already causing problems.

Also, please, please separate your formatting from the database. If I have problems with the spacing in the layout, one of the LAST places I would look at is a database query.

Saving separate sections of your application allows you to simultaneously solve one problem and change the display of your data without worrying about the data itself.

Answer 3

This can simply be done using the application logic after receiving the whole data with duplicate records and splitting them into PHP . Always remember that you can have several application servers to distribute your load, but you always prefer to have one database server on which you want the minimum load of queries. Also, the client load can be distributed between application servers, where as many clients will access the same database.

Answer 4

You need to add DISTINCT to GROUP_CONCAT because you get lines n from certs to m lines from serv , resulting in n * m with a lot of duplicates.

But when you do your GROUP_CONCAT for each table using Derived Tables, you get a row one for each table:

 select u.Name ,c.Id ,c.certsName ,s.Name ,s.Rank ,s.Country from users u left join ( select UserId ,GROUP_CONCAT(Id SEPARATOR '<br>') AS Id ,GROUP_CONCAT(certsName SEPARATOR '<br>') AS certsName from certs where UserId = 1 group by UserId ) as c on u.UserId = c.UserId left join ( select UserId ,GROUP_CONCAT(Name SEPARATOR '<br>') AS Name ,GROUP_CONCAT(Rank SEPARATOR '<br>') AS Rank ,GROUP_CONCAT(Country SEPARATOR '<br>') AS Country from serv where UserId = 1 group by UserId ) as s on u.UserId = s.UserId where u.UserId = 1 

See Fiddle

Of course, advice on providing individual requests is still the best answer; everything else is just a workaround.

Answer 5

 SELECT u.name, (SELECT GROUP_CONCAT(c.id SEPARATOR '<br>') FROM certs c WHERE c.userId = u.userId) AS Id, (SELECT GROUP_CONCAT(c.CertsName SEPARATOR '<br>') FROM certs c WHERE c.userId = u.userId) AS certsName, (SELECT GROUP_CONCAT(s.Name SEPARATOR '<br>') FROM serv s WHERE s.userId = u.userId) AS Name FROM users u WHERE u.userId = ? 

Answer 6

CHOOSE MULTIPLE EQUATIONS FROM TABLE 1 (TB1) AND TABLE2 (TB2): CM1_1, CM1_2 FROM TABLE1 AND CM2_1, CM2_2, CM2_3 FROM TABLE2

 SELECT CM1_1 , CM1_2 , CM2_1 , CM2_2 , CM2_3 FROM TB1, TB2 WHERE CM1_2 = '1' AND CM1_1 = CM2_1 AND CM2_3 = 'John'