MySQL INNER JOIN select only one row from second table
You need to have a subquery to get their latest date per user ID.
SELECT a.*, c.*
FROM users a
INNER JOIN payments c
ON a.id = c.user_ID
INNER JOIN
(
SELECT user_ID, MAX(date) maxDate
FROM payments
GROUP BY user_ID
) b ON c.user_ID = b.user_ID AND
c.date = b.maxDate
WHERE a.package = 1
How to INNER JOIN only 1 row from second table into the first table WITHOUT using alias
You can do:
select *
from (
select *,
row_number() over(partition by i.vid order by i.updated) as rn
from table_entries e
join table_images i on i.vid = e.vid
where e.model like '%apple%' or e.ext like '%apple'
) x
where rn = 1 -- this is the key filter
order by lastupdated desc
limit 0, 10
Please consider this query could be awfully slow due to the use of LIKE '%text%' in the search condition, specially if the table table_entries has millions of rows.
inner join select only one row from second table base on date
where date_column <= sysdate
or, eventually
where date_column <= trunc(sysdate)
depending on whether there is a time component involved or not.
[EDIT, after you included sample data]
As "today" is 2019-10-13, then see if this helps; you'll need lines from #14 onwards as you already have those tables. BTW, it seems that USERS doesn't play any role in desired result.
SQL> with
2 users (id, name) as
3 (select 1, 'Jeff' from dual union all
4 select 2, 'Geoff' from dual
5 ),
6 payments (user_id, start_date, price) as
7 (select 1, date '2019-10-14', 1000 from dual union all
8 select 1, date '2019-10-11', 3500 from dual union all
9 select 1, date '2019-10-16', 2000 from dual union all
10 select 2, date '2019-10-13', 3500 from dual union all
11 select 2, date '2019-10-12', 6500 from dual
12 ),
13 --
14 temp as
15 (select p.user_id, p.start_date, p.price,
16 row_number() over (partition by user_id order by start_date desc) rn
17 from payments p
18 where p.start_date <= date '2019-10-13'
19 )
20 select user_id, start_date, price
21 from temp
22 where rn = 1;
USER_ID START_DATE PRICE
---------- ---------- ----------
1 2019-10-11 3500
2 2019-10-13 3500
SQL>
MySQL inner join limit 1 row from second table
Untested, but this should work.
SELECT
a.xid,
a.cusid,
a.xdate,
a.xref,
-- if distinct currency in the group is > 1 then the word 'multi', else currency.
IF(COUNT(DISTINCT b.xcur) > 1, 'multi', b.xcur) AS `xcur`,
b.xsell,
b.xbuy,
a.xtotal
FROM exchange a
JOIN exchitems b ON a.xid = b.xid AND a.cusid = b.cusid
WHERE a.cusid = 1
GROUP BY xid -- will let you have exchange rows with groups of exchitems 1:n
ORDER BY a.xdate DESC, a.xid DESC
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 ;
inner join select only one row from second table base on IF-THEN-ELSE statement
Use row_number():
select p.*
from (select p.*,
row_number() over (partition by user_id
order by (case when state = 'green' then 1 else 2 end),
date asc
) as seqnum
from payments p
where state = 'green' or
(state = 'yellow' and amount > 1000)
) p
where seqnum = 1;
This assumes that you only want these three priorities. That is, if no rows match, you don't want the user.
Related Topics
Sql Query to Get Number of Times a Field Repeats for Another Specific Field
Sqlcmd Not Able to Find a Library (Libmsodbcsql-17.0.So.1.1) That Is There
Sql - Return a Default Value When My Search Returns No Results Along With Search Criteria
How to Convert Yyyy-Mm-Dd Hh:Mm:Ss to Mm-Dd-Yyyy Hh:Mm:Ss in SQL Server
Postgresql Error: Relation Already Exists
Sql - Sum of Positive and Negative Numbers Using Subquery
Two Rows With the Same Id and Two Different Values, Getting the Second Value into Another Column
Sql: Find Country Name of the Team Having the Most Players Who Have Never Scored a Goal
How to Convert Bigint (Unix Timestamp) to Datetime in SQL Server
Mysql Query - Records Between Today and Last 30 Days
How to Calculate Difference of Values in a Successive Rows
Concat(Column) Over(Partition By...)? Group-Concatentating Rows Without Grouping the Result Itself
Sql Average from Multiple Columns
How to Convert Nvarchar to Numeric in SQL Server
Unable to Get Min and Max Time from a Datetime Column from Every Backday
How to Combine Two Completely Different SQL Queries into One Result