Complicated SQL Query--Finding Items Matching Multiple Different Foreign Keys

Complicated SQL Query--finding items matching multiple different foreign keys

Provided that you have unique indices on both (ProductID, CategoryID) and (ProductID, InvoiceID):

SELECT  ProductID
FROM    (
        SELECT  ProductID
        FROM    ProductInvoice
        WHERE   InvoiceID IN (1, 2)
        UNION ALL
        SELECT  ProductID
        FROM    ProductCategory pc
        WHERE   CategoryID IN (3, 4)
        ) q
GROUP BY
        ProductID
HAVING  COUNT(*) = 4

or, if your values are passed in CSV strings:

WITH    catids(value) AS
        (
        SELECT  DISTINCT CAST([value] AS INT)
        FROM    dbo.split(@categories, ' '))
        ), 
        (
        SELECT  DISTINCT CAST([value] AS INT)
        FROM    dbo.split(@invoices, ' '))
        )
SELECT  ProductID
FROM    (
        SELECT  ProductID
        FROM    ProductInvoice
        WHERE   InvoiceID IN
                (
                SELECT  value
                FROM    invoiceids
                )
        UNION ALL
        SELECT  ProductID
        FROM    ProductCategory pc
        WHERE   CategoryID IN
                (
                SELECT  value
                FROM    catids
                )
        ) q
GROUP BY
        ProductID
HAVING  COUNT(*) = 
        (
        SELECT  COUNT(*)
        FROM    catids
        ) + 
        (
        SELECT  COUNT(*)
        FROM    invoiceids
        )

Note that in SQL Server 2008 you can pass table-valued parameters into the stored procedures.

Selecting records from Foreign Key table where multiple items match the condition

Something like this:

SELECT G.ContentID
FROM (
SELECT PT.ContentID, PT.TagID
FROM PaperTags AS PT
WHERE PT.TagID IN (15, 18)
GROUP BY PT.ContentID, PT.TagID
) AS G
GROUP BY G.ContentId
HAVING Count(*) = 2

MySQL - Trying to get multiple rows matching duplicate foreign keys as additional columns

Ideally, such data display related requirements should be generally solved using application code (eg: PHP, C++, Java, etc). However, based on your comments, you need to do it with SQL only. Moreover, you are confirming that you are concerned with at-most 2 addresses of a customer.

In MySQL version < 8.0.2, we can emulate Row_Number() using User-defined variables. In a Derived table, we will get all the addresses of a customer. It is important to get the data in a particular order in this subquery, so that outer subquery can utilize it for calculating row number values within a partition of customer_id correctly.

In the outer subquery, we will assign row number conditionally using CASE..WHEN expressions. In the outermost subquery, we will do conditional aggregation over a group of customer_id. address_1 and address_2 will be determined when the row number is 1; and address_3 and address_4 will be determined when the row number is 2

SELECT 
  dt2.customer_id, 
  dt2.customer_name, 
  MAX(CASE WHEN dt2.row_no = 1 THEN dt2.address_1 END) AS address_1,
  MAX(CASE WHEN dt2.row_no = 1 THEN dt2.address_2 END) AS address_2,
  MAX(CASE WHEN dt2.row_no = 2 THEN dt2.address_1 END) AS address_3,
  MAX(CASE WHEN dt2.row_no = 2 THEN dt2.address_2 END) AS address_4
FROM 
(
  SELECT 
    @rn := CASE WHEN @cid = dt.customer_id THEN @rn + 1
                ELSE 1
           END AS row_no, 
    @cid := dt.customer_id AS customer_id, 
    dt.customer_name, 
    dt.address_1, 
    dt.address_2
  FROM 
  (
    SELECT
      c.customer_id,
      c.name AS 'customer_name',
      a.address_1,
      a.address_2 
    FROM
      customer c
    LEFT JOIN 
      address a ON c.customer_id = a.customer_id
    ORDER BY c.customer_id, a.address_id  -- this ordering is important
  ) AS dt
  CROSS JOIN (SELECT @cid := 0, @rn := 0) AS user_init_vars -- initialize variables
) AS dt2
GROUP BY dt2.customer_id, dt2.customer_name 
ORDER BY dt2.customer_id 

SQL query to find products matching a set of categories

You could eliminate the performance problems of grouping and counting if you stored that information somewhere. You could add a column to Products called total_categories that will tell you how many categories the product participates in. Then you could just say where total_categories = 4. This might be more difficult to maintain if products are often changing their categories because you'd have to constantly update this field correctly - and then you have to decide if you want to do that in application code or in a trigger or in a stored procedure...

Normally I would not think it a very good idea to store such metadata directly in a table, but if the performance is really that bad, it might be worth considering.

Storing Multiple Values (Foreign Key References) in One Column Oracle

There is, but - you should normalize data model (so - you shouldn't do what you meant to). "Solution" is to create a new table, e.g. ORDER_ITEMS:

create table order_items
  (order_id  number constraint fk_oi_order references orders (order_id),
   item_id   number constraint fk_oi_item  references items  (item_id),
   --
   constraint pk_oi primary key (order_id, item_id)
  );

It would contain combination of order ID and all items it contains.

Multiple foreign keys to a single column

No, you can't have a single field as a foreign key to two different tables. How would you tell where to look for the key?

You would at least need a field that tells what kind of user it is, or two separate foreign keys.

You could also put the information that is common for all users in one table and have separate tables for the information that is specific for the user types, so that you have a single table with user id as primary key.

Complex SQL query over multiple table

This will give the correct result (SQLFiddle):

SELECT 
   gr_reports.id, 
   gr_reports.title, 
   gr_courses.title,
   COUNT(gr_grades.id),
   (SELECT COUNT(1) FROM gr_grades_list WHERE gr_grades_list.report_grade_id = gr_courses.id)
FROM gr_reports
JOIN gr_courses      ON gr_courses.report_id             = gr_reports.id
JOIN gr_grades       ON gr_grades.report_id              = gr_reports.id
JOIN gr_courses_list ON gr_courses_list.report_course_id = gr_courses.id
WHERE gr_courses_list.course_id = 145
GROUP BY gr_reports.id, gr_courses.id

No need to LEFT JOIN courses_list when you have its column course_id in the WHERE clause (where there is no match, course_id is NULL and the row will be filtered out). The LEFT JOIN with gr_grades_list multiplied the COUNT of the gr_grades.id (could be fixed in your query with COUNT(DISTINCT gr_grades.id), but theCOUNT of gr_grades_list would still be wrong with DISTINCT).

---

Related Topics