MYSQL Select from tables based on multiple rows
You need to join with user_meta once for each attribute you want to match.
SELECT u.*
FROM users AS u
JOIN user_meta AS m1 ON u.id = m1.userID
JOIN user_meta AS m2 ON u.id = m2.userID
WHERE m1.meta_key = 'companyID' AND m1.meta_value = :companyID
AND m2.meta_key = 'user_type' AND m2.meta_value = 'staff'
MySQL select row from one table with multiple rows in a second table and get array of multi row in selected row
What you want a fairly straightforward SELECT query with some LEFT/INNER JOIN(s).
This website has some good examples/explanations which seem very close to your need: https://www.mysqltutorial.org/mysql-inner-join.aspx
I would give you a quick working example, but it is not really clear to me what datatype the relevant columns are. Both tables' _id-columns are likely some variant of INTEGER, are they also both primary keys (or otherwise atleast indexed ?), the client_name/ticket_name are likely VARCHAR/TEXT/STRING types, but how exactly is the remaining column stored? as json or array or ? (+details)
Also you tagged your post with PHP, are you just after the SQL query ? or looking for PHP code with the SQL inside it.
updated
Improved version of the schema
CREATE TABLE clients (
client_id SERIAL,
client_name VARCHAR(255) NOT NULL,
PRIMARY KEY (client_id)
);
CREATE TABLE tickets (
ticket_id SERIAL,
ticket_name VARCHAR(255) NOT NULL,
ticket_price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (ticket_id)
);
-- A junction table to glue those 2 tables together (N to N relationship)
CREATE TABLE client_tickets (
client_id BIGINT UNSIGNED NOT NULL,
ticket_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (client_id, ticket_id)
);
I have changed the datatypes.client_name and ticket_name are still VARCHARS. I've flagged them as NOT NULL (eg: required fields), but you can remove that part if you don't like that.client_id/ticket_id/ticket_price are also NOT NULL but changing that has negative side-effects.
ticket_price is now a DECIMAL field, which can store numbers such as 1299.50 or 50.00 The (10,2) bit means it covers every possible number up to 8 whole digits (dollars/euros/whatever), and 2 decimals (cents). so you can store anything from $ -99.999.999,99 to $ 99.999.999,99 .
in SQL always write numbers (like lets say 70k) in this notation: 70000.00 (eg: a dot, not a comma; and no thousandseperators).
client_id and ticket_id are both SERIALs now, which is shorthand for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE and theyre both PRIMARY KEYs on top of that. That probably sounds complicated but they're still just ordinary INTEGERs with values like 4 or 12 etc.
The UNIQUE bit prevents you from having 2 clients with the same ID number, and the AUTO_INCREMENT means that when you add a new client, you dont have to specify an ID (though you are allowed to); you can just do:
INSERT INTO clients (client_name) values ('Fantastic Mr Fox');
and the client_id will automatically be set (incrementing over time). And the same goes for ticket_id in the other table.
.
I've replaced your original client_tickets column, into a separate junction table.
Records in there store the client_id of a client and the ticket_id that belongs to them.
A client can have multiple records in the junction table (one record for each ticket they own).
Likewise, a ticket can be mentioned on any number of rows.
It's possible for a certain client_id to not have any records in the junction table.
Likewise, it's possible for a certain ticket_id to not have any records in the junction table.
Identical records cannot exist in this table (enforced by PRIMARY KEY).
Testdata
Next, we can put some data in there to be able to test it:
-- Create some tickets
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (1, 'ticketone', '30' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (2, 'tickettwo', '40' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (3, 'ticketthree', '50' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (4, 'ticketfour', '60' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (5, 'ticketfive', '70' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (6, 'ticketsix', '4' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (7, 'ticketseven', '9' );
INSERT INTO tickets (ticket_id, ticket_name, ticket_price) values (8, 'ticketeight', '500' );
-- Create some users, and link them to some of these tickets
INSERT INTO clients (client_id, client_name) values (1, 'John');
INSERT INTO client_tickets (client_id, ticket_id) values (1, 3);
INSERT INTO client_tickets (client_id, ticket_id) values (1, 7);
INSERT INTO client_tickets (client_id, ticket_id) values (1, 1);
INSERT INTO clients (client_id, client_name) values (2, 'Peter');
INSERT INTO client_tickets (client_id, ticket_id) values (2, 5);
INSERT INTO client_tickets (client_id, ticket_id) values (2, 2);
INSERT INTO client_tickets (client_id, ticket_id) values (2, 3);
INSERT INTO clients (client_id, client_name) values (3, 'Eddie');
INSERT INTO client_tickets (client_id, ticket_id) values (3, 8);
INSERT INTO clients (client_id, client_name) values (9, 'Fred');
-- Note: ticket #3 is owned by both client #1/#2;
-- Note: ticket #4 and #6 are unused;
-- Note: client #9 (Fred) has no tickets;
Queries
Get all the existing relationships (ticket-less clients are left out & owner-less tickets are left out)
SELECT clients.*
, tickets.*
FROM client_tickets AS ct
INNER JOIN clients ON ct.client_id = clients.client_id
INNER JOIN tickets ON ct.ticket_id = tickets.ticket_id
ORDER BY clients.client_id ASC
, tickets.ticket_id ASC ;
Get all the tickets that are still free (owner-less)
SELECT tickets.*
FROM tickets
WHERE tickets.ticket_id NOT IN (
SELECT ct.ticket_id
FROM client_tickets AS ct
)
ORDER BY tickets.ticket_id ASC ;
Get a list of ALL clients (even ticketless ones), and include how many tickets each has and the total price of their tickets.
SELECT clients.*
, COALESCE(COUNT(tickets.ticket_id), 0) AS amount_of_tickets
, COALESCE(SUM(tickets.ticket_price), 0.00) AS total_price
FROM clients
LEFT JOIN client_tickets AS ct ON ct.client_id = clients.client_id
LEFT JOIN tickets ON ct.ticket_id = tickets.ticket_id
GROUP BY clients.client_id
ORDER BY clients.client_id ASC ;
Put all the juicy info together (owner-less tickets are left out)
SELECT clients.*
, COALESCE(COUNT(sub.ticket_id), 0) AS amount_of_tickets
, COALESCE(SUM(sub.ticket_price), 0.00) AS total_price
, JSON_ARRAYAGG(sub.js_tickets_row) AS js_tickets_rows
FROM clients
LEFT JOIN client_tickets AS ct ON ct.client_id = clients.client_id
LEFT JOIN (
SELECT tickets.*
, JSON_OBJECT( 'ticket_id', tickets.ticket_id
, 'ticket_name', tickets.ticket_name
, 'ticket_price', tickets.ticket_price
) AS js_tickets_row
FROM tickets
) AS sub ON ct.ticket_id = sub.ticket_id
GROUP BY clients.client_id
ORDER BY clients.client_id ASC ;
-- sidenote: output column `js_tickets_rows` (a json array) may contain NULL values
An list of all tickets with some aggregate data
SELECT tickets.*
, IF(COALESCE(COUNT(clients.client_id), 0) > 0
, TRUE, FALSE) AS active
, COALESCE( COUNT(clients.client_id), 0) AS amount_of_clients
, IF(COALESCE( COUNT(clients.client_id), 0) > 0
, GROUP_CONCAT(clients.client_name SEPARATOR ', ')
, NULL) AS client_names
FROM tickets
LEFT JOIN client_tickets AS ct ON ct.ticket_id = tickets.ticket_id
LEFT JOIN clients ON ct.client_id = clients.client_id
GROUP BY tickets.ticket_id
ORDER BY tickets.ticket_id ASC
, clients.client_id ASC ;
MySQL: Selecting data based on values from multiple rows
select distinct t.name, t.toy
from your_table t
where name in
(
select name
from your_table
where toy in ('bat','baseball')
group by name
having count(distinct toy) = 2
)
and toy in ('bat','baseball')
If you just need the name you can do
select name
from your_table
where toy in ('bat','baseball')
group by name
having count(distinct toy) = 2
SQLFiddle demo
Search a table based on multiple rows in another table
I'm going with the assumption that "searching multiple fields" is talking about the Entity-Attribute-Value structure.
In that case, I propose that the first step is to create a derived query - basically, we want to limit the "EAV data joined" to only include the records that have the values we are interested in finding. (I've altered some column names, but the same premise holds.)
SELECT d.userId
FROM data d
JOIN fields f
ON f.fieldId = d.fieldId
-- now that we establish data/field relation, filter rows
WHERE f.type = "location" AND d.value = "london"
OR f.type = "job" AND d.value = "programmer"
This resulting rows are derived from the filtered EAV triplets that match our conditions. Only the userId is selected in this case (as it will be used to join against the user relation), but it is also possible to push fieldId/value/etc through.
Then we can use all of this as a derived query:
SELECT *
FROM users u
JOIN (
-- look, just goes in here :)
SELECT DISTINCT d.userId
FROM data d
JOIN fields f
ON f.fieldId = d.fieldId
WHERE f.type = "location" AND d.value = "london"
OR f.type = "job" AND d.value = "programmer"
) AS e
ON e.userId = u.userId
Notes:
- The query planner will figure all the RA stuff out peachy keen; don't worry about this "nesting" as there is no dependent subquery.
- I avoid the use of implicit cross-joins as I feel they muddle most queries, this case being a particularly good example.
- I've "cheated" and added a DISTINCT to the derived query. This will ensure that at most one record will be joined/returned per user and avoids the use of GROUP BY.
While the above gets "OR" semantics well (it's both easier and I may have misread the question), modifications are required to get "AND" semantics. Here are some ways that the derived query can be written to get such. (And at this point I must apologize to Tony - I forget that I've already done all the plumbing to generate such queries trivially in my environment.)
Count the number of matches to ensure that all rows match. This will only work if each entity is unique per user. It also eliminates the need for DISTINCT to maintain correct multiplicity.
SELECT d.userId
FROM data d
JOIN fields f
ON f.fieldId = d.fieldId
-- now that we establish data/field relation, filter rows
WHERE f.type = "location" AND d.value = "london"
OR f.type = "job" AND d.value = "programmer"
GROUP BY d.userId
HAVING COUNT(*) = 2
Find the intersecting matches:
SELECT d.userId
FROM data d
JOIN fields f ON f.fieldId = d.fieldId
WHERE f.type = "location" AND d.value = "london"
INTERSECT
SELECT d.userId
FROM data d
JOIN fields f ON f.fieldId = d.fieldId
WHERE f.type = "job" AND d.value = "programmer"
Using JOINS (see Tony's answer).
SELECT d1.userId
FROM data d1
JOIN data d2 ON d2.userId = d1.userId
JOIN fields f1 ON f1.fieldId = d1.fieldId
JOIN fields f2 ON f2.fieldId = d2.fieldId
-- requires AND here across row
WHERE f1.type = "location" AND d1.value = "london"
AND f2.type = "job" AND d2.value = "programmer"
An inner JOIN itself provides conjunction semantics when applied outside of the condition. In this case I show "re-normalize" the data. This can also be written such that [sub-]selects appear in the select clause.
SELECT userId
FROM (
-- renormalize, many SO questions on this
SELECT q1.userId, q1.value as location, q2.value as job
FROM (SELECT d.userId, d.value
FROM data d
JOIN fields f ON f.fieldId = d.fieldId
WHERE f.type = "location") AS q1
JOIN (SELECT d.userId, d.value
FROM data d
JOIN fields f ON f.fieldId = d.fieldId
WHERE f.type = "job") AS q2
ON q1.userId = q2.userId
) AS q
WHERE location = "london"
AND job = "programmer"
The above duplicity is relatively easy to generate via code and some databases (such as SQL Server) support CTEs which make writing such much simpler. YMMV.
MYSQL: Select entries in relation to multiple rows of another table
SELECT e.ID
FROM entries e
INNER JOIN entriesmeta m
ON e.ID = m.entry_id
WHERE
m.metakey = 'service' AND
m.metavalue IN (7,13,9,27)
GROUP BY e.ID
HAVING COUNT(DISTINCT m.metavalue) = 4
- SQLFiddle Demo
This query will display all ID that has metakey of service and a value(s) of 7, 13, 9, 27.
Mysql select row based on multiple rows in same table
This problem is called Relational Division.
SELECT item_ID
FROM tableName
WHERE value IN (1,3,4)
GROUP BY item_ID
HAVING COUNT(*) = 3
if uniqueness was not enforce on column value for every item_id, DISTINCT is required to count only unique values,
SELECT item_ID
FROM tableName
WHERE value IN (1,3,4)
GROUP BY item_ID
HAVING COUNT(DISTINCT value) = 3
- SQLFiddle Demo (both query included)
- SQL of Relational Division
Select rows that matches multiple and/or conditions
You can join the 3 tables, group by product and set the conditions in the HAVING clause:
SELECT p.id, p.name
FROM products p
INNER JOIN product_tags_link pt ON pt.product_id = p.id
INNER JOIN tags t ON pt.tag_id = t.id
GROUP BY p.id, p.name
HAVING SUM(t.tag = 'color' AND t.value IN ('green', 'red')) > 0
AND SUM(t.tag = 'pet' AND t.value IN ('dog')) > 0
See the demo.
Related Topics
How to Find Number of Mondays or Tuesdays Between Two Dates
PHP How to Retrieve Array Values
Proper Repository Pattern Design in PHP
MySQL and PHP - Insert Null Rather Than Empty String
Category Hierarchy (Php/Mysql)
Issue in Installing PHP7.2-Mcrypt
PHP Pass Function Name as Param Then Call the Function
Sent Mails with PHPmailer Don't Go to "Sent" Imap Folder
Bad Request. Connecting to Sites via Curl on Host and System
Rest API Authorization & Authentication (Web + Mobile)
How to Include External CSS and Js File in Laravel 5
Mysql_Escape_String VS MySQL_Real_Escape_String
S3 Direct Upload Restricting File Size and Type
Wampserver PHPmyadmin Maximum Execution Time of 360 Seconds Exceeded